import java.sql.*;
import java.io.*;
import java.util.*;
import person.*;

/** SP data loader :
 *  The data for S, P, SP table are stored in sp_data.txt with the following format:
	TABLENAME: S : 4
	s6:  Wang:           99: Bakersfield
	.....

	TABLENAME: P : 5
	p1:  Nut:            Red:       12: London
	....

	TABLENAME: SP : 3
	s6: p6:  6
	....
*/

public class LoadSPData {

   Connection		cnn = null;
   PreparedStatement	pStmt = null;
   Statement		stmt = null;
   ResultSet		res = null ;
   ResultSetMetaData	meta = null;
   String		tableName;
   int			fieldCount;

   String url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
       user="cs440", passwd="student1";

   public LoadSPData() {
      try {
                try { Class.forName("oracle.jdbc.driver.OracleDriver"); }
      catch (ClassNotFoundException ee) { ee.printStackTrace(); System.exit(-1); }
      cnn = DriverManager.getConnection(url, user, passwd);
      } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
   }

   void buildPreparedStatement(String line) {
       StringTokenizer tkz = new StringTokenizer(line, ":");
       /**
        *  Skip "TABLENAME", get table name and number of fields.
        *  Build INSERT statement, and prepare the insert statement.
        */
       tableName = tkz.nextToken();
       tableName = tkz.nextToken().trim();
       fieldCount = Integer.parseInt(tkz.nextToken().trim());
       StringBuffer buf = new StringBuffer();
       buf.append("INSERT INTO " + tableName + " VALUES(?");
       for ( int i = 1; i < fieldCount; i++ ) buf.append(", ?");
       buf.append(")");
       try {
		if ( pStmt != null ) pStmt.close();
       		pStmt = cnn.prepareStatement(buf.toString());
       } catch ( SQLException e ) { e.printStackTrace(); }

       System.out.println("SQL statement: " + buf.toString());
   }

   boolean addRecordToCurrentTable(String line ) {
      StringTokenizer tkz = new StringTokenizer(line, ":");
      try {
	pStmt.clearParameters();
	for ( int i = 1; i <= fieldCount; i ++ )
		pStmt.setString(i, tkz.nextToken().trim());
	pStmt.execute();
      } catch ( SQLException e ) { e.printStackTrace() ; return false; }
      return true;
   }

   public static void main(String argv[]) throws IOException {
      LoadSPData ldr = new LoadSPData(); // get connected, and create stmt.
      boolean    succ;
      String  line;
      BufferedReader spFile = new BufferedReader(new FileReader("sp_data.txt"));
      while ( (line = spFile.readLine()) != null ) {
	 if ( line.trim().equals("") ) continue;      // skip blank line.
         if ( line.trim().indexOf("TABLENAME") == 0 ) 
            ldr.buildPreparedStatement(line);
         else {
		succ = ldr.addRecordToCurrentTable(line);
		if ( succ ) System.out.println(line + " inserted successfully" );
		else System.out.println(line + " insertion failure");
              }
      }

      ldr.quit();
   }

   protected void quit() {
      try {
      pStmt.close();
      // stmt.close();
      // res.close();
      cnn.close();
      System.exit(0);
      } catch (SQLException e) {}
      try { super.finalize(); } catch (Throwable e) {}
   }
}
